<!DOCTYPE html>
<html lang="zh" class="no-js">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<meta http-equiv="x-ua-compatible" content="ie=edge">
<meta name="description" content="book">
<meta name="generator" content="Paradox, paradox-material-theme=0.6.0, mkdocs-material=3.0.3">

<meta name="lang:clipboard.copy" content="Copy to clipboard">
<meta name="lang:clipboard.copied" content="Copied to clipboard">
<meta name="lang:search.language" content="">
<meta name="lang:search.pipeline.stopwords" content="true">
<meta name="lang:search.pipeline.trimmer" content="true">
<meta name="lang:search.result.none" content="No matching documents">
<meta name="lang:search.result.one" content="1 matching document">
<meta name="lang:search.result.other" content="# matching documents">
<meta name="lang:search.tokenizer" content="[\s\-]+">


<meta name="description" content="book">
<link rel="shortcut icon" href="../assets/images/favicon.png">
<title>使用 JDBC 访问 PostgreSQL · Scala Web 开发——基于Akka HTTP</title>
<link rel="stylesheet" href="../assets/stylesheets/application.451f80e5.css">
<link rel="stylesheet" href="../assets/stylesheets/application-palette.22915126.css">
<meta name="theme-color" content="#009688" />
<link rel="stylesheet" href="../lib/material__tabs/dist/mdc.tabs.min.css">
<link rel="stylesheet" href="../lib/prettify/prettify.css">
<script src="../assets/javascripts/modernizr.1aa3b519.js"></script>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto:300,400,400i,700|Roboto+Mono">
<style>
body,input{font-family:"Roboto","Helvetica Neue",Helvetica,Arial,sans-serif}
code,kbd,pre{font-family:"Roboto Mono","Courier New",Courier,monospace}
</style>
<link rel="stylesheet" href="../assets/fonts/font-awesome.css">
<link rel="stylesheet" href="../assets/fonts/material-icons.css">
<link rel="stylesheet" href="../assets/stylesheets/paradox-material-theme.css">
</head>
<body
data-md-color-primary="teal"
data-md-color-accent="indigo"
>
<input class="md-toggle" data-md-toggle="drawer" type="checkbox" id="__drawer" autocomplete="off">
<input class="md-toggle" data-md-toggle="search" type="checkbox" id="__search" autocomplete="off">
<label class="md-overlay" data-md-component="overlay" for="__drawer"></label>
<header class="md-header" data-md-component="header">
<nav class="md-header-nav md-grid">
<div class="md-flex">
<div class="md-flex__cell md-flex__cell--shrink">
<a href="../index.html" title="Scala Web 开发——基于Akka HTTP" class="md-header-nav__button md-logo">
<i class="md-icon">local_library</i>
</a>
</div>
<div class="md-flex__cell md-flex__cell--shrink">
<label class="md-icon md-icon--menu md-header-nav__button" for="__drawer"></label>
</div>
<div class="md-flex__cell md-flex__cell--stretch">
<div class="md-flex__ellipsis md-header-nav__title" data-md-component="title">
<span class="md-header-nav__topic">
Scala Web 开发——基于Akka HTTP
</span>
<span class="md-header-nav__topic">
使用 JDBC 访问 PostgreSQL
</span>
</div>
</div>
<div class="md-flex__cell md-flex__cell--shrink">
<label class="md-icon md-icon--search md-header-nav__button" for="__search"></label>
<div class="md-search" data-md-component="search" role="dialog">
<label class="md-search__overlay" for="__search"></label>
<div class="md-search__inner" role="search">
<form class="md-search__form" name="search">
<input type="text" class="md-search__input" name="query" placeholder="Search" autocapitalize="off" autocorrect="off" autocomplete="off" spellcheck="false" data-md-component="query" data-md-state="active">
<label class="md-icon md-search__icon" for="__search"></label>
<button type="reset" class="md-icon md-search__icon" data-md-component="reset" tabindex="-1">&#xE5CD;</button>
</form>
<div class="md-search__output">
<div class="md-search__scrollwrap" data-md-scrollfix>
<div class="md-search-result" data-md-component="result">
<div class="md-search-result__meta">
Type to start searching
</div>
<ol class="md-search-result__list"></ol>
</div>
</div>
</div>
</div>
</div>

</div>
<div class="md-flex__cell md-flex__cell--shrink">
<div class="md-header-nav__source">
<a href="https://github.com/yangbajing/scala-web-development"
title="Go to repository"
class="md-source"
data-md-source="github">
<div class="md-source__icon">
<i class="fa fa-github"></i>
</div>
<div class="md-source__repository">
yangbajing/scala-web-development
</div>
</a>

</div>
</div>
</div>
</nav>
</header>

<div class="md-container">
<main class="md-main">
<div class="md-main__inner md-grid" data-md-component="container">
<div class="md-sidebar md-sidebar--primary" data-md-component="navigation">
<div class="md-sidebar__scrollwrap">
<div class="md-sidebar__inner">
<nav class="md-nav md-nav--primary" data-md-level="0" style="visibility: hidden">
<label class="md-nav__title md-nav__title--site" for="drawer">
<a href="../index.html" title="Scala Web 开发——基于Akka HTTP" class="md-nav__button md-logo">
<span class="md-nav__button md-logo">
<i class="md-icon">local_library</i>
</a>
<a href="../index.html" title="Scala Web 开发——基于Akka HTTP">
Scala Web 开发——基于Akka HTTP
</a>
</label>
<div class="md-nav__source">
<a href="https://github.com/yangbajing/scala-web-development"
title="Go to repository"
class="md-source"
data-md-source="github">
<div class="md-source__icon">
<i class="fa fa-github"></i>
</div>
<div class="md-source__repository">
yangbajing/scala-web-development
</div>
</a>

</div>
<ul>
  <li><a href="../preface.html" class="page">前言</a></li>
  <li><a href="../env/index.html" class="page">Scala 环境配置</a>
  <ul>
    <li><a href="../env/env.1.html" class="page">Sbt</a></li>
    <li><a href="../env/env.2.html" class="page">IDE开发工具</a></li>
    <li><a href="../env/env.z.html" class="page">小结</a></li>
  </ul></li>
  <li><a href="../scala/index.html" class="page">Scala 语言基础</a>
  <ul>
    <li><a href="../scala/scala.0.html" class="page">REPL</a></li>
    <li><a href="../scala/scala.1.html" class="page">你好，Scala</a></li>
    <li><a href="../scala/scala.2.html" class="page">Scala基础</a></li>
    <li><a href="../scala/scala.3.html" class="page">流程和函数</a></li>
    <li><a href="../scala/scala.4.html" class="page">集合</a></li>
    <li><a href="../scala/scala.5.html" class="page">class和object</a></li>
    <li><a href="../scala/scala.6.html" class="page">函数式</a></li>
    <li><a href="../scala/scala.7.html" class="page">Trait</a></li>
    <li><a href="../scala/scala.8.html" class="page">并发</a></li>
    <li><a href="../scala/scala.z.html" class="page">小结</a></li>
  </ul></li>
  <li><a href="../basic/index.html" class="page">Akka HTTP 基础</a>
  <ul>
    <li><a href="../basic/basic.0.html" class="page">Akka HTTP 基础</a></li>
    <li><a href="../basic/basic.1.html" class="page">Web 工作方式</a></li>
    <li><a href="../basic/basic.2.html" class="page">使用 Akka Http 搭建一个简单的 Web 服务</a></li>
    <li><a href="../basic/basic.3.html" class="page">Akka HTTP 的通用抽象</a></li>
    <li><a href="../basic/basic.z.html" class="page">小结</a></li>
  </ul></li>
  <li><a href="../server-api/index.html" class="page">服务端API</a>
  <ul>
    <li><a href="../server-api/work.html" class="page">Akka HTTP 如何使得 Web 工作</a></li>
    <li><a href="../server-api/advanced.html" class="page">高级服务端 API</a></li>
  </ul></li>
  <li><a href="../routing-dsl/index.html" class="page">路由DSL</a>
  <ul>
    <li><a href="../routing-dsl/route.html" class="page">Route 路由</a></li>
    <li><a href="../routing-dsl/directive.html" class="page">Directive 指令</a></li>
    <li><a href="../routing-dsl/custom-directive.html" class="page">自定义指令</a></li>
    <li><a href="../routing-dsl/rejections.html" class="page">拒绝 rejections</a></li>
    <li><a href="../routing-dsl/exception.html" class="page">异常处理</a></li>
    <li><a href="../routing-dsl/file-upload.html" class="page">实战：大文件断点上传、下载和秒传</a></li>
  </ul></li>
  <li><a href="../directives/index.html" class="page">常用指令</a>
  <ul>
    <li><a href="../directives/path.html" class="page">PathDirectives（路径指令）</a></li>
    <li><a href="../directives/method.html" class="page">directives/method.html</a></li>
    <li><a href="../directives/parameter_form.html" class="page">directives/parameter_form.html</a></li>
    <li><a href="../directives/marshalling.html" class="page">directives/marshalling.html</a></li>
    <li><a href="../directives/file.html" class="page">directives/file.html</a></li>
    <li><a href="../directives/cookie.html" class="page">directives/cookie.html</a></li>
  </ul></li>
  <li><a href="../data/index.html" class="page">数据</a>
  <ul>
    <li><a href="../data/data.0.html" class="page">数据</a></li>
    <li><a href="../data/data.1.html" class="page">JSON</a></li>
    <li><a href="../data/data.ant-design-pro.html" class="page">实战：为Ant Design Pro提供后端接口</a></li>
    <li><a href="../data/data.kryo.html" class="page">Kryo</a></li>
    <li><a href="../data/data.2.html" class="page">Protobuf</a></li>
    <li><a href="../data/data.z.html" class="page">小结</a></li>
  </ul></li>
  <li><a href="../test/index.html" class="page">测试</a>
  <ul>
    <li><a href="../test/test.0.html" class="page">测试</a></li>
    <li><a href="../test/test.1.html" class="page">Scalatest</a></li>
    <li><a href="../test/test.2.html" class="page">测试异步代码</a></li>
    <li><a href="../test/test.3.html" class="page">端到端测试Route</a></li>
    <li><a href="../test/test.z.html" class="page">小结</a></li>
  </ul></li>
  <li><a href="../actor/index.html" class="page">Akka Actor</a>
  <ul>
    <li><a href="../actor/actor.html" class="page">Akka Typed Actor</a></li>
    <li><a href="../actor/actor-test.html" class="page">Akka Actor 测试</a></li>
    <li><a href="../actor/actor.z.html" class="page">Actor小结</a></li>
  </ul></li>
  <li><a href="../oauth/index.html" class="page">实战：实现OAuth 2服务</a>
  <ul>
    <li><a href="../oauth/oauth.0.html" class="page">实战：OAuth 2 服务</a></li>
    <li><a href="../oauth/oauth.1.html" class="page">OAuth 2简介</a></li>
    <li><a href="../oauth/oauth.2.html" class="page">OAuth 2接口设计</a></li>
    <li><a href="../oauth/oauth.3.html" class="page">OAuth 2服务实现</a></li>
    <li><a href="../oauth/oauth.z.html" class="page">小结</a></li>
  </ul></li>
  <li><a href="../database/index.html" class="page">访问数据库</a>
  <ul>
    <li><a href="../database/database.0.html" class="page">访问数据库</a></li>
    <li><a href="../database/database.1.html" class="active page">使用 JDBC 访问 PostgreSQL</a></li>
    <li><a href="../database/database.2.html" class="page">使用 Slick 访问数据库</a></li>
    <li><a href="../database/database.3.html" class="page">访问 Cassandra 数据库</a></li>
    <li><a href="../database/database.4.html" class="page">访问 Redis</a></li>
    <li><a href="../database/database.5.html" class="page">访问 Elasticsearch</a></li>
    <li><a href="../database/database.z.html" class="page">小结</a></li>
  </ul></li>
  <li><a href="../engineering/index.html" class="page">工程化</a>
  <ul>
    <li><a href="../engineering/swagger.html" class="page">使用Swagger编写API文档</a></li>
    <li><a href="../engineering/guice.html" class="page">使用Guice管理类依赖</a></li>
  </ul></li>
  <li><a href="../grpc/index.html" class="page">Akka gRPC</a>
  <ul>
    <li><a href="../grpc/grpc.html" class="page">gRPC服务</a></li>
    <li><a href="../grpc/build-tool.html" class="page">构建工具</a></li>
    <li><a href="../grpc/deployment.html" class="page">部署</a></li>
    <li><a href="../grpc/grpc.z.html" class="page">小结</a></li>
  </ul></li>
  <li><a href="../config-discovery/index.html" class="page">实战：配置管理、服务发现系统</a></li>
  <li><a href="../appendix/index.html" class="page">附录</a>
  <ul>
    <li><a href="../appendix/appendix.0.html" class="page">参考资料</a></li>
    <li><a href="../appendix/appendix.1.html" class="page">专业术语</a></li>
    <li><a href="../appendix/appendix.2.html" class="page">词汇表</a></li>
  </ul></li>
  <li><a href="../donate.html" class="page">赞助</a></li>
</ul>
<nav class="md-nav md-nav--secondary">
<label class="md-nav__title" for="__toc">Table of contents</label>
<ul>
  <li><a href="../database/database.1.html#使用-jdbc-访问-postgresql" class="header">使用 JDBC 访问 PostgreSQL</a>
  <ul>
    <li><a href="../database/database.1.html#postgresql" class="header">PostgreSQL</a></li>
    <li><a href="../database/database.1.html#访问数据库" class="header">访问数据库</a></li>
  </ul></li>
</ul>
</nav>

</nav>
<ul style="display: none">
<li class="md-nav__item md-version" id="project.version">
<label class="md-nav__link" for="__version">
<i class="md-icon" title="Version">label_outline</i> 1.0.0
</label>
</li>
</ul>
</div>
</div>
</div>
<div class="md-sidebar md-sidebar--secondary" data-md-component="toc">
<div class="md-sidebar__scrollwrap">
<div class="md-sidebar__inner">
<nav class="md-nav md-nav--secondary">
<label class="md-nav__title" for="__toc">Table of contents</label>
<ul>
  <li><a href="../database/database.1.html#使用-jdbc-访问-postgresql" class="header">使用 JDBC 访问 PostgreSQL</a>
  <ul>
    <li><a href="../database/database.1.html#postgresql" class="header">PostgreSQL</a></li>
    <li><a href="../database/database.1.html#访问数据库" class="header">访问数据库</a></li>
  </ul></li>
</ul>
</nav>

</div>
</div>
</div>
<div class="md-content">
<article class="md-content__inner md-typeset">
<div class="md-content__searchable">
<h1><a href="#使用-jdbc-访问-postgresql" name="使用-jdbc-访问-postgresql" class="anchor"><span class="anchor-link"></span></a>使用 JDBC 访问 PostgreSQL</h1>
<p>在 Scala 中，Java 的数据库访问方式都可以使用，比如：JDBC、JPA、MyBatis、Hibernate等。除此之外，也有些专为 Scala 制作的数据库访问库，如： Slick、ScalikeJDBC、Quill等。但这里，我们介绍在 Scala 中怎样使用 JDBC。</p>
<h2><a href="#postgresql" name="postgresql" class="anchor"><span class="anchor-link"></span></a>PostgreSQL</h2>
<p>首先，安装 <strong>PostgreSQL</strong> 数据库。</p>
<h3><a href="#linux" name="linux" class="anchor"><span class="anchor-link"></span></a>Linux</h3>
<pre class="prettyprint"><code class="language-bash">apt-get install postgresql // Debian/Ubuntu
yum install postgresql // Fedora/CentOS/RHEL
</code></pre>
<h3><a href="#mac" name="mac" class="anchor"><span class="anchor-link"></span></a>Mac</h3>
<pre class="prettyprint"><code class="language-bash">brew install postgresql
</code></pre>
<h3><a href="#windows" name="windows" class="anchor"><span class="anchor-link"></span></a>Windows</h3>
<p>到 EnterpriseDB 官网下载二进制安装包：<code>[https://www.enterprisedb.com/downloads/postgres-postgresql-downloads#windows](https://www.enterprisedb.com/downloads/postgres-postgresql-downloads#windows)</code></p>
<h3><a href="#配置网络" name="配置网络" class="anchor"><span class="anchor-link"></span></a>配置网络</h3>
<p>Linux下，PostgreSQL默认是不允许通过远程网络访问的。我们需要修改下它的默认配置。</p>
<ul>
  <li><em>Ubunux</em>：<code>/etc/postgresql/9.6/main</code> 目录</li>
  <li><em>CentOS</em>：<code>/var/lib/pgsql/data</code> 目录</li>
</ul>
<ol>
  <li>编辑 <code>postgresql.conf</code> 文件，去掉 <code>listen_address</code> 的注释，并修改为：</li>
</ol>
<pre><code>listen_address = &#39;*&#39;
</code></pre>
<p>同时也去掉 <code>password_encryption</code> 的注释。</p>
<ol>
  <li>编辑 <code>pg_hba.conf</code> 文件，添加：<code>host    all      all    0.0.0.0/0    md</code> ，设置允许所有网络访问并使用 <code>md5</code> 形式的密码登录机制。</li>
</ol>
<blockquote>
  <p>（注意：修改PostgreSQL的配置文件后需要重启数据库以使其生效）</p>
</blockquote>
<h3><a href="#创建账号及初使化库-表" name="创建账号及初使化库-表" class="anchor"><span class="anchor-link"></span></a>创建账号及初使化库、表</h3>
<h4><a href="#创建账号-数据库" name="创建账号-数据库" class="anchor"><span class="anchor-link"></span></a>创建账号、数据库</h4>
<pre><code>sudo su - postgres
psql
postgres=# create user yangbajing nosuperuser encrypted password &#39;yangbajing&#39;;
postgres=# create database akkahttp owner=yangbajing template=template0 encoding=&#39;UTF-8&#39; lc_collate=&#39;zh_CN.UTF-8&#39; lc_ctype=&#39;zh_CN.UTF-8&#39;;
</code></pre>
<h4><a href="#初始化表-数据" name="初始化表-数据" class="anchor"><span class="anchor-link"></span></a>初始化表、数据</h4>
<pre class="prettyprint"><code class="language-sql">CREATE TABLE author(
  id bigserial,
  name VARCHAR(255) NOT NULL,
  age int,
  remark TEXT,
  PRIMARY KEY (id)
);

CREATE TABLE book(
  isbn VARCHAR(64),
  author bigint NOT NULL,
  title VARCHAR(255) NOT NULL,
  description VARCHAR(1024),
  amount DECIMAL NOT NULL,
  PRIMARY KEY (isbn)
);
</code></pre>
<h2><a href="#访问数据库" name="访问数据库" class="anchor"><span class="anchor-link"></span></a>访问数据库</h2>
<h3><a href="#命令行" name="命令行" class="anchor"><span class="anchor-link"></span></a>命令行</h3>
<pre><code>psql -h localhost -d hldev -U hldev
</code></pre>
<h3><a href="#代码访问" name="代码访问" class="anchor"><span class="anchor-link"></span></a>代码访问</h3>
<p>首先，我们定义两个数据库实体对象：<code>Author</code> 和 <code>Book</code>：</p>
<pre class="prettyprint"><a class="icon go-to-source" href="https://github.com/yangbajing/scala-web-development/tree/master/foundation/src/main/scala/akkahttp/foundation/data/entity/Author.scala#L4" target="_blank" title="Go to snippet source"></a><code class="language-scala">case class Author(id: Long, name: String, age: Option[Int], remark: Option[String])</code></pre>
<pre class="prettyprint"><a class="icon go-to-source" href="https://github.com/yangbajing/scala-web-development/tree/master/foundation/src/main/scala/akkahttp/foundation/data/entity/Book.scala#L4-L9" target="_blank" title="Go to snippet source"></a><code class="language-scala">case class Book(
    isbn: String,
    author: Long,
    title: String,
    amount: BigDecimal,
    description: Option[String])</code></pre>
<p>接下来在 <code>AuthorRepository</code> 中进行数据库操作，现在先把什么 <code>Service</code>，<code>Controller</code>等放一边，我们使用 <strong>scalatest</strong> 来测试下我们的数 据库访问代码。</p>
<pre class="prettyprint"><a class="icon go-to-source" href="https://github.com/yangbajing/scala-web-development/tree/master/foundation/src/test/scala/akkahttp/foundation/data/repository/AuthorRepositoryTest.scala#L11-L35" target="_blank" title="Go to snippet source"></a><code class="language-scala">class AuthorRepositoryTest extends MeSpec with JDBCSpec {
  &quot;AuthorRepositoryTest&quot; should {
    val accountRepository = new AuthorRepository(dataSource)

    &quot;create&quot; in {
      val account = Author(0, &quot;羊八井&quot;, Some(31), None)
      val result = accountRepository.create(account)
      result.id should be &gt; 0L
    }

    &quot;update&quot; in {
      val author = Author(1, &quot;羊八井&quot;, Some(32), None)
      val result = accountRepository.update(author)
      result.id shouldBe author.id
      result.name shouldBe author.name
      result.age shouldBe author.age
    }

    &quot;list&quot; in {
      val results = accountRepository.list()
      results should not be empty
      println(s&quot;results size: ${results.size}&quot;)
    }
  }
}</code></pre>
<p>先不管 <code>MeSpec</code> 和 <code>JDBCSpec</code> 两个 trait，你可以先试试这个测试（前题是你已经设置好数据库环境）。</p>
<pre class="prettyprint"><code class="language-scala">&gt; testOnly akkahttp.foundation.data.repository.AuthorRepositoryTest
</code></pre>
<p><img src="../assets/imgs/testOnly-AuthorRepositoryTest.png" alt="testOnly akkahttp.foundation.data.repository.AuthorRepositoryTest" /></p>
<p>若一切正常，你会看到测试正确通过。<code>AuthorRepositoryTert</code> 脚本中一共执行了3个测试，其中 <code>should list</code> 测试中打印了一个测试输出，从数据库中 获取到8条记录。下面，让我们来看看 <code>AuthorRepository</code> ，我们怎么在 Scala 中使用 JDBC 来访问数据库的。</p>
<pre class="prettyprint"><a class="icon go-to-source" href="https://github.com/yangbajing/scala-web-development/tree/master/foundation/src/main/scala/akkahttp/foundation/data/repository/AuthorRepository.scala#L15-L54" target="_blank" title="Go to snippet source"></a><code class="language-scala">class AuthorRepository(dataSource: DataSource) {
  val jdbcTemplate = JdbcTemplate(dataSource)

  def update(author: Author): Author = {
    require(author.id &gt; 0L, &quot;id 必有大于 0&quot;)

    val (names, args) = AuthorRepository.generateArgs(author)
    val updateSet = JdbcTemplate.sqlUpdateSets(names)
    val sql = s&quot;UPDATE author SET $updateSet WHERE id = ? RETURNING *&quot;
    args.append(author.id.asInstanceOf[Object])

    val (results, _) = jdbcTemplate.queryMany(sql, args)
    if (results.isEmpty) {
      throw new SQLException(s&quot;账号：${author.id} 不存在&quot;)
    } else {
      AuthorRepository.generateResult(results.head)
    }
  }

  def create(author: Author): Author = {
    require(author.id &lt;= 1L, &quot;id 不能存在&quot;)

    val (names, args) = AuthorRepository.generateArgs(author)
    val sql =
      s&quot;&quot;&quot;INSERT INTO author(${JdbcTemplate.sqlNames(names)})
         |  VALUES(${JdbcTemplate.sqlArgs(args)}) RETURNING id&quot;&quot;&quot;.stripMargin

    val (results, labels) = jdbcTemplate.queryMany(sql, args)

    val id = results.head.apply(labels.head.label).asInstanceOf[Long]
    author.copy(id = id)
  }

  def list(): Vector[Author] = {
    val (results, _) =
      jdbcTemplate.queryMany(
        &quot;SELECT id, name, age, remark FROM author ORDER BY id DESC&quot;)
    results.map(AuthorRepository.generateResult)
  }
}</code></pre>
<ol>
  <li>这里我们没有使用 Ioc 等方式来管理组件件的依赖，我们在类的构造代码区里实例华一个 <code>jdbcTemplate</code>。</li>
  <li>在 <code>update</code> 方法的开头，我们效验 Author <code>id</code> 是否有效。</li>
  <li>主意这里的 SQL 语句，在普通的 <code>update</code> 语句之上添加了 <code>RETURNING *</code> 语句。这里 <strong>PostgreSQL</strong> 数据库的一个扩展特性，它将返回语句影 响记录的所有字段。可以通过这个特性很方便的在更新语句之后获取到最新的数据。</li>
  <li><code>generateResult</code> 函数，把返回的键值对形式的数据转换映射成一个 <code>Author</code> 对象</li>
  <li>这里使用 <code>RETURNING id</code> 返回数据库序列自动生成的 Author <code>id</code></li>
</ol>
<p>在 <code>AuthorRepository</code> 的伴生对象里，有两个简单的帮助函数，<code>generateResult</code> 和 <code>generateArgs</code> ，分别将通过JDBC的ResultSet获取的记录映射 成一个 <code>Author</code> 对象和将 <code>author</code> 对象转换成可用于 <code>INSERT</code>、<code>UPDATE</code>语句字段序列部分及获取有效参数的一个序列。这段话说起来有点拗口，直接来 看看 <code>generateArgs</code> 的代码：</p>
<pre class="prettyprint"><a class="icon go-to-source" href="https://github.com/yangbajing/scala-web-development/tree/master/foundation/src/main/scala/akkahttp/foundation/data/repository/AuthorRepository.scala#L59-L90" target="_blank" title="Go to snippet source"></a><code class="language-scala">def generateResult(result: Map[String, AnyRef]): Author =
  Author(
    result(&quot;id&quot;).asInstanceOf[Long],
    result(&quot;name&quot;).asInstanceOf[String],
    result.get(&quot;age&quot;).map(_.asInstanceOf[Int]),
    result.get(&quot;remark&quot;).map(_.asInstanceOf[String]))

/**
 * 获取有效的数据库查询字段名列表和参数值列表
 *
 * @param author Author实例
 * @return
 */
def generateArgs(
    author: Author): (mutable.Buffer[String], mutable.Buffer[Object]) = {
  val names = mutable.Buffer.empty[String]
  val args = mutable.Buffer.empty[Object]

  names.append(&quot;name&quot;)
  args += author.name

  author.age.foreach { value =&gt;
    names.append(&quot;age&quot;)
    args += Integer.valueOf(value)
  }

  author.remark.foreach { remark =&gt;
    names.append(&quot;remark&quot;)
    args += remark
  }
  (names, args)
}</code></pre>
<p>我们有一个 Author 对象的实例：Author(0, &ldquo;羊八井&rdquo;, Some(31), None)。在调用 <code>generateArgs</code> 函数后，将获得以下转出结果：</p>
<pre class="prettyprint"><code class="language-js">([&quot;name&quot;, &quot;age&quot;], [&quot;羊八井&quot;, 32])
</code></pre>
<p>我们获得了一个 Tuple（元组），第一个值是字段名的序列，第二个值是数据（参数）序列。有了这两个序列后，再使用 <code>JdbcTemplate.sqlNames</code> 和  <code>JdbcTemplate.sqlArgs</code> 来简化我们 SQL 语句的拼装。</p>
<pre class="prettyprint"><code class="language-scala">  // [name, age] =&gt; &quot;name&quot; = ?, &quot;age&quot; = ?
  def sqlUpdateSets(names: Seq[String]): String = names.map(name =&gt; s&quot;&quot;&quot;&quot;$name&quot; = ?&quot;&quot;&quot;).mkString(&quot;, &quot;)

  // [name, age] =&gt; &quot;name&quot;, &quot;age&quot;
  def sqlNames(names: Seq[String]): String = names.mkString(&quot;\&quot;&quot;, &quot;\&quot;, \&quot;&quot;, &quot;\&quot;&quot;)

  // [value1, value2] =&gt; ?, ?
  def sqlArgs(args: Seq[_]): String = args.map(_ =&gt; &quot;?&quot;).mkString(&quot;, &quot;)
</code></pre>
<p>经过这些简单的函数步骤，我们就可以很方便的拼接出想要的SQL语句了。</p>
<pre class="prettyprint"><code class="language-scala">// INSERT INTO author(name, age) VALUES(?, ?) RETURNING id
val sql = &quot;&quot;&quot;INSERT INTO author(${JdbcTemplate.sqlNames(names)}) VALUES(${JdbcTemplate.sqlArgs(args)}) RETURNING id&quot;&quot;&quot;
</code></pre>
<p>JDBC是Java里标准的SQL访问方式，Scala 作为一门 JVM 语言是天然支持的。而 PostgreSQL 是开源界里优秀的关系型数据库，也是功能做为丰富的。这里我们 知道了在 Scala 里面怎样使用 SQL 来访问关系型数据库，下一节将介绍怎样访问 Cassandra 这一强大的 NoSQL 数据库。</p>
</div>
<div>
<a href="https://github.com/yangbajing/scala-web-development/tree/master/book/src/main/paradox/database/database.1.md" title="Edit this page" class="md-source-file md-edit">
Edit this page
</a>
</div>
<div class="print-only">
<span class="md-source-file md-version">
1.0.0
</span>
</div>
</article>
</div>
</div>
</main>
<footer class="md-footer">
<div class="md-footer-nav">
<nav class="md-footer-nav__inner md-grid">
<a href="../database/database.0.html" title="访问数据库" class="md-flex md-footer-nav__link md-footer-nav__link--prev" rel="prev">
<div class="md-flex__cell md-flex__cell--shrink">
<i class="md-icon md-icon--arrow-back md-footer-nav__button"></i>
</div>
<div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title">
<span class="md-flex__ellipsis">
<span class="md-footer-nav__direction">
Previous
</span>
访问数据库
</span>
</div>
</a>
<a href="../database/database.2.html" title="使用 Slick 访问数据库" class="md-flex md-footer-nav__link md-footer-nav__link--next" rel="next">
<div class="md-flex__cell md-flex__cell--stretch md-footer-nav__title">
<span class="md-flex__ellipsis">
<span class="md-footer-nav__direction">
Next
</span>
使用 Slick 访问数据库
</span>
</div>
<div class="md-flex__cell md-flex__cell--shrink">
<i class="md-icon md-icon--arrow-forward md-footer-nav__button"></i>
</div>
</a>
</nav>
</div>
<div class="md-footer-meta md-typeset">
<div class="md-footer-meta__inner md-grid">
<div class="md-footer-copyright">
Powered by
<a href="https://github.com/lightbend/paradox">Paradox</a>
and
<a href="https://jonas.github.io/paradox-material-theme/">Paradox Material Theme</a>

</div>
<div class="md-footer-social">
<a href="https://github.com/yangbajing" class="md-footer-social__link fa fa-github"></a><a href="https://weibo.com/yangbajing" class="md-footer-social__link fa fa-globe"></a><a href="https://www.yangbajing.me/" class="md-footer-social__link fa fa-globe"></a>
</div>

</div>
</div>
</footer>

</div>
<script src="../assets/javascripts/application.583bbe55.js"></script>
<script src="../assets/javascripts/paradox-material-theme.js"></script>
<script>app.initialize({version:"0.17",url:{base:"../."}})</script>
<script type="text/javascript" src="../lib/prettify/prettify.js"></script>
<script type="text/javascript" src="../lib/prettify/lang-scala.js"></script>
<script type="text/javascript">
document.addEventListener("DOMContentLoaded", function(event) {
window.prettyPrint && prettyPrint();
});
</script>
</body>
</html>
